﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month')
	BEGIN
		DROP Procedure usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month
**	Desc : 계정별 전월대비 증감액
**	Test Exec Query : Exec usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month 'CC06X,CC07X', '2011-01', '2011-02', 'ko'
**	Called by : Crd_Dac_UPDMS_CRD_REPORT_Crd3013p.cs
**	Program ID : Crd3013p
**	Auth : 송시명
**	Date : 2010-07-27
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month]
@ls_card_cd nvarchar(MAX),
@ls_pre_month nvarchar(7),
@ls_this_month nvarchar(7),
@ls_lang_set nvarchar(2)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @sql nvarchar(max)

SET @sql = '
SELECT Account,
       Account_Nm,
       Pre_Total,
       ''<span'' + CASE WHEN Pre_Total  > 0 THEN '' onclick="javascript:viewDetailList(''''' + @ls_card_cd + ''''','''''' + Account + '''''',''''' + @ls_pre_month + ''''');">'' ELSE '' onclick="javascript:void(0);">''  END  + dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR, Pre_Total)) + ''</span>'' AS Pre_Total_Link,
       This_Total,
       ''<span'' + CASE WHEN This_Total  > 0 THEN '' onclick="javascript:viewDetailList(''''' + @ls_card_cd + ''''','''''' + Account + '''''',''''' + @ls_this_month + ''''');">'' ELSE '' onclick="javascript:void(0);">''  END  + dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR, This_Total)) + ''</span>'' AS This_Total_Link,
       Diff_Total
  FROM (
       SELECT Account,
              dbo.ufn_UPDMS_Get_Code_Name(Account, ''CRD'', @ls_lang_set) AS Account_Nm,
              ISNULL([' + @ls_pre_month + '],0) AS Pre_Total,
              ISNULL([' + @ls_this_month + '],0) AS This_Total,
              ISNULL([' + @ls_this_month + '],0) - ISNULL([' + @ls_pre_month + '],0) AS Diff_Total
         FROM (
              SELECT x.Account,
                     x.Paymnt_Dt,
                     SUM(ISNULL(x.Total, 0)) AS Total
                FROM (
                     SELECT LEFT(Paymnt_Dt, 7) AS Paymnt_Dt,
                            Account,
                            SUM(ISNULL(Amount, 0)) AS Total
                       FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
                      WHERE Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                        AND LEFT(Paymnt_Dt, 7) IN (@ls_pre_month, @ls_this_month)
                      GROUP BY Account, Paymnt_Dt
                     UNION ALL
                     SELECT LEFT(ucir.Paymnt_Dt, 7),
                            ucib.Account,
                            ucir.Ins_Month_Amt AS Total
                       FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)
                       JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib WITH(NOLOCK) ON ucir.Ins_Cd = ucib.Ins_Cd
                      WHERE ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                        AND LEFT(ucir.Paymnt_Dt, 7) IN (@ls_pre_month, @ls_this_month)
                     ) x
               GROUP BY x.Paymnt_Dt, x.Account
              ) pv
        PIVOT ( MAX(pv.Total) FOR pv.Paymnt_Dt IN ([' + @ls_pre_month + '], [' + @ls_this_month + '])
              ) AS pvt
       ) z
 ORDER BY z.Account
'

DECLARE @param nvarchar(max)
SET @param = '
  @ls_card_cd nvarchar(max),
  @ls_pre_month nvarchar(7),
  @ls_this_month nvarchar(7),
  @ls_lang_set nvarchar(2)
'

--print(@sql)
EXEC sp_executesql @sql, @param,
                   @ls_card_cd = @ls_card_cd,
                   @ls_pre_month = @ls_pre_month,
                   @ls_this_month = @ls_this_month,
                   @ls_lang_set = @ls_lang_set

GO